SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE VIEW dbo.[DnnProduct_vw_Lists]
AS
	SELECT     
		EntryID, 
		ListName, 
		Value, 
		Text, 
		[Level], 
		SortOrder, 
		DefinitionID, 
		ParentID, 
		Description,
		PortalID, 
		SystemList,
		dbo.DnnProduct_GetListParentKey(ParentID, ListName, N'ParentKey', 0) AS ParentKey, 
		dbo.DnnProduct_GetListParentKey(ParentID, ListName, N'Parent', 0) AS Parent, 
		dbo.DnnProduct_GetListParentKey(ParentID, ListName, N'ParentList', 0) AS ParentList,
		(SELECT MAX(SortOrder) FROM dbo.[DnnProduct_Lists] WHERE (ListName = L.ListName) AND (ParentID = L.ParentID)) AS MaxSortOrder,
		(SELECT COUNT(EntryID) FROM dbo.[DnnProduct_Lists] AS DnnProduct_Lists_1 WHERE (ListName = L.ListName) AND (ParentID = L.ParentID)) AS EntryCount,
		(SELECT COUNT(DISTINCT ParentID) FROM dbo.[DnnProduct_Lists] AS DnnProduct_Lists_2 WHERE (ParentID = L.EntryID)) AS HasChildren,
		L.CreatedByUserID,
		L.CreatedOnDate,
		L.LastModifiedByUserID,
		L.LastModifiedOnDate
	FROM  dbo.[DnnProduct_Lists] AS L

GO
